from sqlalchemy import text
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import select
from sqlalchemy.sql.expression import ClauseElement
from faker import Faker
from crm.commons import *
import copy
import random
from datetime import datetime


def get_or_create(session, model, defaults=None, **kwargs):
    """如果不存在则创建，如果存在则返回
    输入参数：
        session: sqlalchemy session
        model: 自定义的ORM类
        defaults: 有默认值的字段
        kwargs: 其他字段(必须包含主要字段)
    返回值：
        instance: 返回的实例
    """
    try:
        instance = session.query(model).filter_by(**kwargs).first()
        if instance:
            print("instance already exists", instance)
            return instance
        else:
            params = dict((k, v) for k, v in kwargs.items()
                          if not isinstance(v, ClauseElement))
            if defaults:
                params.update(defaults)
            instance = model(**params)

            session.add(instance)
            session.commit()
            print("instance inserted", instance)
    except Exception as e:
        print("***Insert failed **** \n", str(e))
        return None
    return instance


# Faker.seed(999)
faker = Faker(['zh_CN', 'en_US'])
engine = create_engine(
    "mysql+mysqlconnector://root:Admin&123@localhost:3306/people")

# 用automap_base类从已存在数据库中自动映射
Base = automap_base()
Base.prepare(autoload_with=engine)

Company = Base.classes.company
Customers = Base.classes.customers
Opportunity = Base.classes.opportunity

SCALE_LIST = ['LR', "ML", 'SM', "UN"]
GENDER_LIST = [1, 2, 3]


def add_customers():
    with Session(engine) as session:
        # 插入数据
        # for i in range(1, 3):
        #     get_or_create(session, Company, name=faker.company(),
        #                   mainbusiness=faker.bs(), scale=random.choice(SCALE_LIST))
        stmt = select(Company)
        results = session.scalars(stmt)
        objs = copy.copy(results)
        companys = list(objs)
        print(len(companys))
        print("first obj ", companys[0].name)
        # 遍历结果
        for r in companys:
            print(r.name, r.mainbusiness, r.scale)
        faker.seed_locale("en_US")
        for i in range(1, 4000):
            cnty = ''
            while True:
                cnty = faker.country()
                if len(cnty) < 50:
                    break
            get_or_create(session, Customers,
                          name=faker.name(),
                          gender=random.choice(GENDER_LIST),
                          age=random.randint(22, 55),
                          company=random.choice(companys),
                          phone=faker.phone_number(),
                          address=faker.street_address(),
                          city=faker.city(),
                          country=cnty,
                          postcode=faker.postalcode(),
                          creditlimit=random.randrange(10000, 1000000, 1000)
                          )
        stmt = select(Customers)
        results = session.scalars(stmt)
        print(f"total rows: { len(results.all()) }")


def add_opportunity():
    # 插入数据
    with Session(engine) as session:
        stmt = select(Company)
        results = session.scalars(stmt)
        objs = copy.copy(results)
        companys = list(objs)
        faker.seed_locale("zh_CN")
        for i in range(1, 80):
            select_company = random.choice(companys)
            print(select_company.id, select_company.name)
            data = {
                'name': "".join(faker.words(nb=5)),
                'company': select_company,
                'amount': random.randrange(10000, 1000000, 1000),
                'lead_source': random.choice(OPP_SOURCES)[1],
                'probability': random.randint(1, 100),
                'description': faker.sentence(),
                'status': random.choice(OPP_STATUS_CHOICES)[1],
                'created_at': datetime.now(),
                'updated_at': datetime.now(),
            }
            try:
                obj = Opportunity(**data)
                print(obj.name, obj.amount)
                session.add(obj)
                session.commit()
                print("inserted", obj.name, obj.amount)
            except Exception as e:
                print("Insert failed", str(e))

def add_opp_contact():
    with Session(engine) as session:
        # get all company objects
        stmt = select(Company)
        results = session.scalars(stmt)
        companys = results.all()
        stmt = select(Opportunity)
        results = session.scalars(stmt)
        opportunities = results.all()
        stmt = select(Customers)
        results = session.scalars(stmt)
        customers = results.all()
    stmt = text(
        "INSERT INTO opportunity_contacts(opportunity_id, customers_id) VALUES(:x, :y)")

    with engine.connect() as conn:
        for i in range(1, 100):
            try:
                opp_obj = random.choice(opportunities)
                # Get customer list related to Opportunitiy.company_id, then choose one
                stmt_customers = select(Customers).join(Customers.company).where(
                    Company.id == opp_obj.company_id)
                customer_list = session.scalars(stmt_customers).all()
                x1 = opp_obj.id
                y1 = random.choice(customer_list).id
                stmt_insert = stmt.bindparams(x=x1, y=y1)
                print(stmt_insert)
                conn.execute(stmt_insert)
                conn.commit()
            except Exception as e:
                print("insert failed. ", str(e))


add_opp_contact()
